import os
import xlrd
import sys
import django
import datetime
from xlrd import xldate_as_tuple

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.extend([BASE_DIR, ])
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "finance_c2c.settings")
django.setup()
import xlwt
# from apps.product.models import Product
from apps.order.models import BaseOrder
from apps.sales_man.models import Channel


# from django.db.models import Sum
# from core.tools.mysql_connect import SqlModel
# 脱敏数据表

class ExportChannelOrder(object):
    def __init__(self, book, sheet, start_time=None, end_time=None):
        self.channel = Channel.objects.all()
        self.sheet = sheet
        self.header_line = ["渠道名称", "业务员姓名", "产品名称", "客户姓名", "客户手机号", "核卡/放款日期"]
        self.book = book
        self.start_time = start_time
        self.end_time = end_time

    def get_order(self):
        self.write_header()
        for i in self.channel:
            order = BaseOrder.objects.filter(salesman__channel=i)
            if self.start_time:
                order = order.filter(create_time__gte=self.start_time)
            if self.end_time:
                order = order.filter(create_time__lt=self.end_time)
            self.write_data(order)
        return self.book

    def write_header(self):
        leader_row = 0
        for header in self.header_line:
            self.sheet.write(0, leader_row, header)
            leader_row += 1

    def write_data(self, order):
        row = 1
        # order = BaseOrder.objects.all()
        for i in order:
            self.sheet.write(row, 0, i.salesman.channel.name)
            if i.single.applicant.tel == i.salesman.tel:
                # 真实申请人和订单佣金的人是同一个人
                customer = i.salesman.name
                if i.salesman.leader:
                    leader = i.salesman.leader.name
                else:
                    leader = "无上级"
            else:
                customer = i.single.applicant.name
                leader = i.salesman.name
            self.sheet.write(row, 1, leader)
            self.sheet.write(row, 2, i.product.name)
            self.sheet.write(row, 3, customer)
            self.sheet.write(row, 4, i.single.applicant.tel[:3] + "****" + i.single.applicant.tel[-4:])
            self.sheet.write(row, 5, i.create_time.strftime("%Y-%m-%d"))
            row += 1


def write_excel():
    book = xlwt.Workbook()  # 创建excel对象
    sheet1 = book.add_sheet('sheet1')  # 添加一个表
    export_channel_order = ExportChannelOrder(book, sheet1)
    book = export_channel_order.get_order()
    book.save("chanel-order.xls")


if __name__ == '__main__':
    write_excel()
